﻿using System.Collections;
using dpa.Library.Helper;
using dpa.Library.Models;
using dpa.Models;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

namespace dpa.Library.Services;

public class ExtractExcelService(IExcelService excelService) : IExtractExcelService
{
    public async Task<FlightsReleased> ExtractFlightsReleased(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);

        //.MatchText(@"\d+年\d+月")
        var released = new FlightsReleased();

        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "呼和浩特");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            released.Date = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, 0, "D")).MatchText(@"\d+年\d+月");
            released.Count = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "C")).MatchAmount().ToInt32();
            released.Rate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToDecimal();
            released.YearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "U")).MatchAmount().ToDecimal();
            released.MonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "V")).MatchAmount().ToDecimal();
        }

        return released;
    }

    public async Task<AirportInitialFlight> ExtractAirportInitialFlight(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);
        
        var airportInitialFlight = new AirportInitialFlight();

        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 2, "呼和浩特");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            airportInitialFlight.Count = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "D")).MatchAmount().ToInt32();
            airportInitialFlight.Rate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToDecimal();
            airportInitialFlight.YearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "T")).MatchAmount().ToDecimal();
            airportInitialFlight.MonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "U")).MatchAmount().ToDecimal();
        }

        return airportInitialFlight;
    }

    public async Task<AirportInitialAllFlight> ExtractAirportInitialAllFlight(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);
        var airportInitialAllFlight = new AirportInitialAllFlight();
        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "呼和浩特");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            airportInitialAllFlight.Rate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "F")).MatchAmount().ToDecimal();
            airportInitialAllFlight.YearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToDecimal();
            airportInitialAllFlight.MonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToDecimal();
        }

        return airportInitialAllFlight;
    }

    public async Task<AirportBridgingRate2> ExtractAirportBridgingRate2(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);
        var airportBridgingRate2 = new AirportBridgingRate2();

        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "呼和浩特");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            airportBridgingRate2.LeavePortRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "E")).MatchAmount().ToDecimal();
            airportBridgingRate2.ArrivePortRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToDecimal();
            airportBridgingRate2.TotalRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "K")).MatchAmount().ToDecimal();
            airportBridgingRate2.YearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "L")).MatchAmount().ToDecimal();
            airportBridgingRate2.MonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "M")).MatchAmount().ToDecimal();
        }

        return airportBridgingRate2;
    }

    public async Task<AirlineFlights> ExtractAirlineFlights(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);
        var airlineFlights = new AirlineFlights();

        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "合计");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            airlineFlights.TotalCount = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "C")).MatchAmount().ToInt32();
            airlineFlights.TotalRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "F")).MatchAmount().ToDecimal();
            airlineFlights.YearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "S")).MatchAmount().ToDecimal();
            airlineFlights.MonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "T")).MatchAmount().ToDecimal();
        }

            
        return airlineFlights;
    }

    public async Task<AirlineTakesOff> ExtractAirlineTakesOff(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);
        var airlineTakesOff = new AirlineTakesOff();

        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "合计");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            airlineTakesOff.TotalCount = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "C")).MatchAmount().ToInt32();
            airlineTakesOff.TotalRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "F")).MatchAmount().ToDecimal();
            airlineTakesOff.YearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToDecimal();
            airlineTakesOff.MonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToDecimal();
        }

        return airlineTakesOff;
    }

    public async Task<AirlineDelay> ExtractAirlineDelay(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);
        var airlineDelay = new AirlineDelay();

        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "合计");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            airlineDelay.LessThan30Min = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToInt32();
            airlineDelay.LessThan30MinRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo + 1, "G")).MatchAmount().ToDecimal();
            airlineDelay.Between30MinTo1H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToInt32();
            airlineDelay.Between30MinTo1HRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo + 1, "H")).MatchAmount().ToDecimal();
            airlineDelay.Between1HTo2H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "I")).MatchAmount().ToInt32();
            airlineDelay.Between1HTo2HRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo + 1, "I")).MatchAmount().ToDecimal();
            airlineDelay.Between2HTo4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "J")).MatchAmount().ToInt32();
            airlineDelay.Between2HTo4HRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo + 1, "J")).MatchAmount().ToDecimal();
            airlineDelay.MoreThan4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "K")).MatchAmount().ToInt32();
            airlineDelay.MoreThan4HRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo + 1, "K")).MatchAmount().ToDecimal();
            airlineDelay.AverageDelay = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "L")).MatchAmount().ToDecimal();
            airlineDelay.YearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "M")).MatchAmount().ToDecimal();
            airlineDelay.MonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "N")).MatchAmount().ToDecimal();
        }

        
        return airlineDelay;
    }
    
    public async Task<List<AirlineDelay>> ExtractAirlineDelays(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);

        var airlineDelays = new List<AirlineDelay>();

        var airlineDelay_guohang = new AirlineDelay();
        
        (int rowNo, int cellNo)? findKeyWordInColum_guohang = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "国航");
        if (findKeyWordInColum_guohang is not null)
        {
            var valueRowNo = findKeyWordInColum_guohang.Value.rowNo;
            airlineDelay_guohang.AirlinCompany = "国航";
            airlineDelay_guohang.LessThan30Min = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToInt32();
            airlineDelay_guohang.Between30MinTo1H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToInt32();
            airlineDelay_guohang.Between1HTo2H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "I")).MatchAmount().ToInt32();
            airlineDelay_guohang.Between2HTo4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "J")).MatchAmount().ToInt32();
            airlineDelay_guohang.MoreThan4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "K")).MatchAmount().ToInt32();
            airlineDelays.Add(airlineDelay_guohang);
        }

        
        
        var airlineDelay_tianjiaohang = new AirlineDelay();
        (int rowNo, int cellNo)? findKeyWordInColum_tianjiaohang = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "天骄航");
        if (findKeyWordInColum_tianjiaohang is not null)
        {
            var valueRowNo = findKeyWordInColum_tianjiaohang.Value.rowNo;
            
            airlineDelay_tianjiaohang.AirlinCompany = "天骄航";
            airlineDelay_tianjiaohang.LessThan30Min = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToInt32();
            airlineDelay_tianjiaohang.Between30MinTo1H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToInt32();
            airlineDelay_tianjiaohang.Between1HTo2H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "I")).MatchAmount().ToInt32();
            airlineDelay_tianjiaohang.Between2HTo4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "J")).MatchAmount().ToInt32();
            airlineDelay_tianjiaohang.MoreThan4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "K")).MatchAmount().ToInt32();
            airlineDelays.Add(airlineDelay_tianjiaohang);
        }

        var airlineDelay_tianjinhang = new AirlineDelay();
        (int rowNo, int cellNo)? findKeyWordInColum_tianjinhang = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "天津航");
        if (findKeyWordInColum_tianjinhang is not null)
        {
            var valueRowNo = findKeyWordInColum_tianjinhang.Value.rowNo;
            
            airlineDelay_tianjinhang.AirlinCompany = "天津航";
            airlineDelay_tianjinhang.LessThan30Min = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToInt32();
            airlineDelay_tianjinhang.Between30MinTo1H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToInt32();
            airlineDelay_tianjinhang.Between1HTo2H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "I")).MatchAmount().ToInt32();
            airlineDelay_tianjinhang.Between2HTo4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "J")).MatchAmount().ToInt32();
            airlineDelay_tianjinhang.MoreThan4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "K")).MatchAmount().ToInt32();
            airlineDelays.Add(airlineDelay_tianjinhang);
        }

        
        
        var airlineDelay_huaxiahang = new AirlineDelay();
        (int rowNo, int cellNo)? findKeyWordInColum_huaxiahang = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "华夏航");
        if (findKeyWordInColum_huaxiahang is not null)
        {
            var valueRowNo = findKeyWordInColum_huaxiahang.Value.rowNo;
            
            airlineDelay_huaxiahang.AirlinCompany = "华夏航";
            airlineDelay_huaxiahang.LessThan30Min = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "G")).MatchAmount().ToInt32();
            airlineDelay_huaxiahang.Between30MinTo1H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "H")).MatchAmount().ToInt32();
            airlineDelay_huaxiahang.Between1HTo2H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "I")).MatchAmount().ToInt32();
            airlineDelay_huaxiahang.Between2HTo4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "J")).MatchAmount().ToInt32();
            airlineDelay_huaxiahang.MoreThan4H = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "K")).MatchAmount().ToInt32();
            airlineDelays.Add(airlineDelay_huaxiahang);
        }
        

        return airlineDelays;
    }

    public async Task<AirportTaxiingExtraSlidingOutIn> ExtractAirportTaxiingExtraSlidingOutIn(string filePath)
    {
        HSSFWorkbook hssfWorkbook = excelService.LoadExcel(filePath);
        
        var airportTaxiingExtraSlidingOutIn = new AirportTaxiingExtraSlidingOutIn();

        (int rowNo, int cellNo)? findKeyWordInColum = await excelService.FindKeyWordInColum(hssfWorkbook, 1, "呼和浩特");
        if (findKeyWordInColum is not null)
        {
            var valueRowNo = findKeyWordInColum.Value.rowNo;

            airportTaxiingExtraSlidingOutIn.OutAverageTime = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "D")).MatchAmount().ToDecimal();
            airportTaxiingExtraSlidingOutIn.OutYearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "E")).MatchAmount().ToDecimal();
            airportTaxiingExtraSlidingOutIn.OutMonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "F")).MatchAmount().ToDecimal();
            airportTaxiingExtraSlidingOutIn.InAverageTime = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "J")).MatchAmount().ToDecimal();
            airportTaxiingExtraSlidingOutIn.InYearOnYearRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "K")).MatchAmount().ToDecimal();
            airportTaxiingExtraSlidingOutIn.InMonthOnMonthRate = (await excelService.GetValueByRowNoAndCellNo(hssfWorkbook, valueRowNo, "L")).MatchAmount().ToDecimal();
        }
  
        return airportTaxiingExtraSlidingOutIn;
    }

    public async Task<AirportInfo> ExtractAirportInfo(IEnumerable<FileItem> fileItems)
    {
        var extractFlightsReleased = await ExtractFile(fileItems, FlightsReleased.RegexText().IsMatch, ExtractFlightsReleased);
        var extractAirlineDelay = await ExtractFile(fileItems, AirlineDelay.RegexText().IsMatch, ExtractAirlineDelay);
        var extractAirlineDelays = await ExtractFile(fileItems, AirlineDelay.RegexText().IsMatch, ExtractAirlineDelays);
        var extractAirlineFlights = await ExtractFile(fileItems, AirlineFlights.RegexText().IsMatch, ExtractAirlineFlights);
        var extractAirlineTakesOff = await ExtractFile(fileItems, AirlineTakesOff.RegexText().IsMatch, ExtractAirlineTakesOff);
        var extractAirportBridgingRate2 = await ExtractFile(fileItems, AirportBridgingRate2.RegexText().IsMatch, ExtractAirportBridgingRate2);
        var extractAirportInitialAllFlight = await ExtractFile(fileItems, AirportInitialAllFlight.RegexText().IsMatch, ExtractAirportInitialAllFlight);
        var extractAirportInitialFlight = await ExtractFile(fileItems, AirportInitialFlight.RegexText().IsMatch, ExtractAirportInitialFlight);
        var extractAirportTaxiingExtraSlidingOutIn = await ExtractFile(fileItems, AirportTaxiingExtraSlidingOutIn.RegexText().IsMatch, ExtractAirportTaxiingExtraSlidingOutIn);
        
        var airportInfo = new AirportInfo
        {
            Date = extractFlightsReleased.Date,
            AirportName = "呼和浩特",
            FlightsReleased = extractFlightsReleased,
            AirportInitialFlight = extractAirportInitialFlight,
            AirportInitialAllFlight = extractAirportInitialAllFlight,
            AirportBridgingRate2 = extractAirportBridgingRate2,
            AirlineFlights = extractAirlineFlights,
            AirlineTakesOff = extractAirlineTakesOff,
            AirlineDelay = extractAirlineDelay,
            AirlineDelays = extractAirlineDelays,
            AirportTaxiingExtraSlidingOutIn = extractAirportTaxiingExtraSlidingOutIn
        };

        return airportInfo;
    }
    
    private async Task<T> ExtractFile<T>(IEnumerable<FileItem> files, Func<string, bool> matcher, Func<string, Task<T>> extractor)
    {
        var filePath = files.First(f => matcher(f.FilePath)).FilePath;
        return await extractor(filePath);
    }
}